package persistence.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import common.util.DateUtils;

import core.CoreFacade;
import persistence.DbUtils;
import persistence.TransactionManager;
import persistence.dto.ItemDTO;
import persistence.dto.ProductDTO;
import persistence.dto.ProductGroupDTO;
import persistence.dto.StorageUnitDTO;
import core.Amount;
import core.Barcode;
import core.CoreFacade;
import core.Item;
import core.ItemBarcode;
import core.ModelObject;
import core.Product;
import core.ProductContainer;
import core.ProductGroup;
import core.ProductManager;
import core.RootStorageUnit;
import core.StorageUnit;
import core.Unit;

public class DatabaseDAOFactory extends ModelObject implements IDAOFactory
{
	private static final long serialVersionUID = -8155594089446391994L;

	@Override
	public IProductsDAO getProductsDAO()
	{
		return ProductsDBDAO.getInstance();
	}

	@Override
	public IItemsDAO getItemsDAO()
	{
		return ItemsDBDAO.getInstance();
	}

	@Override
	public IProductGroupsDAO getPGsDAO()
	{
		return ProductGroupsDBDAO.getInstance();
	}

	@Override
	public IStorageUnitsDAO getSUsDAO()
	{
		return StorageUnitsDBDAO.getInstance();
	}

	@Override
	public CoreFacade loadData()
	{
		TransactionManager.begin();
		CoreFacade core = CoreFacade.getInstance();
		StorageUnitsDBDAO SUs = StorageUnitsDBDAO.getInstance();
		ProductGroupsDBDAO PGs = ProductGroupsDBDAO.getInstance();
		ItemsDBDAO items = ItemsDBDAO.getInstance();
		ProductsDBDAO prods = ProductsDBDAO.getInstance();
		ContainerToProductsDBDAO cont2prods = ContainerToProductsDBDAO.getInstance();
		
		List<StorageUnitDTO> suList = new ArrayList<StorageUnitDTO>(SUs.getAllStorageUnits());
		
		// Load StorageUnit / Product Group tree
		for(int i = 0; i < suList.size(); i++)
		{
			try
			{
				StorageUnitDTO dto = suList.get(i);
				StorageUnit su = core.loadStorageUnit(dto.getName(),dto.getId());
				List<ProductGroupDTO> children = new ArrayList<ProductGroupDTO>
				(PGs.getAllChildrenProductGroups(dto.getId()));
				for(ProductGroupDTO child : children)
				{
					// load yourself
					ProductGroup pg = core.loadProductGroup(su, child.getName(), 
							child.getThreeMonthNum(), 
							Unit.createUnit(child.getThreeMonthType()), child.getId());
					// load your children
					loadChildren(child, pg);
				}
				
			} catch (Exception e)
			{
				e.printStackTrace();
			}
		}
		
		// Load Products
		List<ProductDTO> prodList = new ArrayList<ProductDTO>(prods.getAllProducts());
		for(ProductDTO prod : prodList)
		{
			Barcode bCode = new Barcode(prod.getBarcode());
			Amount amt = new Amount(Unit.createUnit(prod.getAmountType()),prod.getAmountNum());
			int shelf_life = prod.getShelfLife();
			int three_month_supply = prod.getThreeMonthSupply();
			Product newProduct = new Product(prod.getCreationDateCal(), 
					bCode, prod.getDescription(), amt,
					 shelf_life, three_month_supply);
			newProduct.setID(prod.getId());
			if(prod.getRemoved())
			{
				// if the product is removed
				ProductManager.getInstance().addRemovedProduct(newProduct);
				System.out.println("Added Removed Product with ID: " + newProduct.getID());
				continue;
				
			}
			// looks in ContainerToProducts Table to get all PC IDs
			TransactionManager.begin();
			List<Integer> PCIDs = cont2prods.getAllContainers(prod.getId());
			
			// just add to system, not in any container
			try
			{
				core.addProduct(newProduct);
			} catch (Exception e)
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		
			
			try {
				// add product to each of the the appropriate Product Containers
				for(Integer i : PCIDs)
				{
					ProductContainer pc = RootStorageUnit.getInstance().getProductContainer(i);
					if(pc == null)
					{
						System.out.println("Can't add PRODUCT to ProductContainer with id: " + i);
						continue;
					}
					core.loadProduct(newProduct, pc, prod.getId());
				}
			} 
			catch (Exception e) 
			{
				System.out.println("Error Loading Product");
			}
		}
		
		// Load Items
		TransactionManager.begin();
		List<ItemDTO> itemList = new ArrayList<ItemDTO>(items.getAllItems());
		for(ItemDTO dto : itemList)
		{ 
			//)
			TransactionManager.begin();
			String productBarcode = prods.getProductBarcode(dto.getProductID());
			Barcode b = new Barcode(productBarcode);
			Product p = ProductManager.getInstance().getProduct(b);
			if(p == null)
			{
				// this means the product is in the removed table
				p = ProductManager.getInstance().getRemovedProduct(productBarcode);
			}
			ProductContainer pc = 
				RootStorageUnit.getInstance().getProductContainer(dto.getParentContainerID());
			if(pc == null && dto.getRemovedFlag())
			{
				ItemBarcode ibar = new ItemBarcode(dto.getBarcode());
				Item item = new Item(p, dto.getEntryDateCal(), ibar);
				item.setID(dto.getId());
				item.setRemovedTime(dto.getExitTimeCal());
				core.addRemovedItem(item);
				continue;
			}
			ItemBarcode ibar = new ItemBarcode(dto.getBarcode());
			Item item = new Item(p,pc, dto.getEntryDateCal(), ibar);
			item.setID(dto.getId());
			core.loadItemIntoContainer(item, pc);
		}
		TransactionManager.begin();
		TransactionManager.end(true);
		return core;
	}
	
	private void loadChildren(ProductGroupDTO dto, ProductGroup parent) throws Exception
	{
		CoreFacade core = CoreFacade.getInstance();
		ProductGroupsDBDAO PGs = ProductGroupsDBDAO.getInstance();
		List<ProductGroupDTO> children = 
			new ArrayList<ProductGroupDTO>(PGs.getAllChildrenProductGroups(dto.getId()));
		for(ProductGroupDTO child : children)
		{
			// load yourself
			ProductGroup pg = core.loadProductGroup(parent, 
					child.getName(), child.getThreeMonthNum(), 
					Unit.createUnit(child.getThreeMonthType()), child.getId());
			// load your children
			loadChildren(child, pg);
		}
	}

	@Override
	public boolean saveData()
	{
		return true;
	}
	/*
	public List<Integer> getContainersContainingProduct(int prodId)
	{
		List<Integer> containerIDs = new ArrayList<Integer>();
		TransactionManager.begin();
		Connection connection = TransactionManager.getConnection();
		String sql = "select containerID from ContainerToProducts where productID = ?";
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try
		{
			stmt = connection.prepareStatement(sql);
			stmt.setInt(1, prodId);
			
			rs = stmt.executeQuery();
			while (rs.next()) 
			{
				containerIDs.add(rs.getInt(1));
			}
		} catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			DbUtils.safeClose(stmt);
			DbUtils.safeClose(rs);
		}
		
		TransactionManager.end(true);
		
		return containerIDs;
		
	}
	*/

	@Override
	public IContainerToProductsDAO getContsToProdsDAO()
	{

		return ContainerToProductsDBDAO.getInstance(); 
	}

}
